/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.tables.Bsc;
import com.elcom.emocbc.dbmapping.tables.ZoneBsc;
import com.elcom.emocbc.dbmapping.tables.daos.BscDao;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class BscPersistence extends BscDao {

    public BscPersistence() throws SQLException {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }
    
    private Bsc BSC = Bsc.BSC;
    
    public List<com.elcom.emocbc.dbmapping.tables.pojos.Bsc> findByBscId(List<String> bscIds) throws SQLException{
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectFrom(BSC).where(BSC.BSC_ID.in(bscIds)).fetchInto(com.elcom.emocbc.dbmapping.tables.pojos.Bsc.class);
        }finally {
            DbAccess.closeConnection(con);
        }
    }
    
    public com.elcom.emocbc.dbmapping.tables.pojos.Bsc findByBscName(String bscName) throws SQLException{
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectFrom(BSC).where(BSC.BSC_NAME.eq(bscName)).fetchOneInto(com.elcom.emocbc.dbmapping.tables.pojos.Bsc.class);
        }finally {
            DbAccess.closeConnection(con);
        }
    }
    
    public List<com.elcom.emocbc.dbmapping.tables.pojos.Bsc> findByBscIdAndCenter(String bscId, String center) throws SQLException{
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectFrom(BSC).where(BSC.BSC_ID.eq(bscId).and(BSC.CENTER.eq(center))).fetchInto(com.elcom.emocbc.dbmapping.tables.pojos.Bsc.class);
        }finally {
            DbAccess.closeConnection(con);
        }
    }
    
    public List<com.elcom.emocbc.dbmapping.tables.pojos.Bsc> findInAllZones() throws SQLException{
        final ZoneBsc ZONE_BSC = ZoneBsc.ZONE_BSC;
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            List<String> bscIds = context.selectDistinct(ZONE_BSC.BSC_ID).from(ZONE_BSC).fetch(ZONE_BSC.BSC_ID);
            return context.selectFrom(BSC).where(BSC.BSC_ID.in(bscIds)).fetchInto(com.elcom.emocbc.dbmapping.tables.pojos.Bsc.class);
        }finally {
            DbAccess.closeConnection(con);
        }
    }
    
    public List<com.elcom.emocbc.dbmapping.tables.pojos.Bsc> findBySmscbCenterId(List<String> smscbCenterIds) throws SQLException{
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectFrom(BSC).where(BSC.CENTER.in(smscbCenterIds)).fetchInto(com.elcom.emocbc.dbmapping.tables.pojos.Bsc.class);
        }finally {
            DbAccess.closeConnection(con);
        }
    }
    
    public List<String> getAllBscNames() throws SQLException{
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectDistinct(BSC.BSC_NAME).from(BSC).fetch(BSC.BSC_NAME);
        }finally {
            DbAccess.closeConnection(con);
        }
    }
    
    public List<String> getAllCenters() throws SQLException{
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectDistinct(BSC.CENTER).from(BSC).fetch(BSC.CENTER);
        }finally {
            DbAccess.closeConnection(con);
        }
    }
    
    public List<String> getAllStatuses() throws SQLException{
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectDistinct(BSC.STATUS).from(BSC).fetch(BSC.STATUS);
        }finally {
            DbAccess.closeConnection(con);
        }
    }
    
    public List<String> getAllVendors() throws SQLException{
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectDistinct(BSC.VENDOR).from(BSC).fetch(BSC.VENDOR);
        }finally {
            DbAccess.closeConnection(con);
        }
    }
//    public int update(BscRecord bscRecord) throws SQLException {
//        Connection con = dbAccess.getConnection();
//        try {
//            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
//            return context.update(BSC)
//                    .set(BSC.BSC_NAME, bscRecord.getBscName())
//                    .set(BSC.CENTER, bscRecord.getCenter())
//                    .set(BSC.CREATED_DATE, bscRecord.getCreatedDate())
//                    .set(BSC.DESCRIPTION, bscRecord.getDescription())
//                    .set(BSC.INIT_TIME, bscRecord.getInitTime())
//                    .set(BSC.PRIMARY_ADDRESS, bscRecord.getPrimaryAddress())
//                    .set(BSC.PRIMARY_PORT, bscRecord.getPrimaryPort())
//                    .set(BSC.PROTOCOL, bscRecord.getProtocol())
//                    .set(BSC.RECONNECTION_NUM, bscRecord.getReconnectionNum())
//                    .set(BSC.STATUS, bscRecord.getStatus())
//                    .set(BSC.UPDATED_DATE, bscRecord.getUpdatedDate())
//                    .set(BSC.USER_ID, bscRecord.getUserId())
//                    .set(BSC.VENDOR, bscRecord.getVendor())
//                    .set(BSC.VERSION, bscRecord.getVersion())
//                    .where(BSC.BSC_ID.equal(bscRecord.getBscId()))
//                    .execute();
//        } finally {
//            DbAccess.commitTransaction(con);
//            DbAccess.closeConnection(con);
//        }
//    }
//
//    public void delete(String bscId) throws SQLException {
//        Connection con = dbAccess.getConnection();
//        try {
//            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
//            context.delete(BSC).where(BSC.BSC_ID.equal(bscId)).execute();
//        } finally {
//            DbAccess.closeConnection(con);
//        }
//    }
//
//    @Override
//    protected String getInsertSql(BscRecord record, DSLContext context) throws SQLException {
//        BscRecord bscRecord = (BscRecord) record;
//        List values = new ArrayList();
//        values.add(bscRecord.getBscId());
//        values.add(bscRecord.getBscName());
//        values.add(bscRecord.getCenter());
//        values.add(bscRecord.getCreatedDate());
//        values.add(bscRecord.getDescription());
//        values.add(bscRecord.getInitTime());
//        values.add(bscRecord.getPrimaryAddress());
//        values.add(bscRecord.getPrimaryPort());
//        values.add(bscRecord.getProtocol());
//        values.add(bscRecord.getReconnectionNum());
//        values.add(bscRecord.getStatus());
//        values.add(bscRecord.getUpdatedDate());
//        values.add(bscRecord.getUserId());
//        values.add(bscRecord.getVendor());
//        values.add(bscRecord.getVersion());
//
//        return context.insertInto(BSC,
//                BSC.BSC_ID,
//                BSC.BSC_NAME,
//                BSC.CENTER,
//                BSC.CREATED_DATE,
//                BSC.DESCRIPTION,
//                BSC.INIT_TIME,
//                BSC.PRIMARY_ADDRESS,
//                BSC.PRIMARY_PORT,
//                BSC.PROTOCOL,
//                BSC.RECONNECTION_NUM,
//                BSC.STATUS,
//                BSC.UPDATED_DATE,
//                BSC.USER_ID,
//                BSC.VENDOR,
//                BSC.VERSION).values(values).getSQL(true);
//    }
}
